/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sg.edu.nus.iss.pacman.dao.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import sg.edu.nus.iss.pacman.constant.Constants;
import sg.edu.nus.iss.pacman.dao.jb.ViewScoreBean;
import sg.edu.nus.iss.pacman.dao.PacManDAO;
import sg.edu.nus.iss.pacman.service.UserProfile;
import sg.edu.nus.iss.pacman.util.PacManUtil;

/**
 *
 * @author Tabiul Mahmood
 */
public class PacManDAOImpl implements PacManDAO
{
    private static boolean init = false;

    public PacManDAOImpl()
    {
        if (!init)
        {
            Connection conn = null;
            try
            {
                String[] sqlStatements = PacManUtil.getFileFromResourceStream(Constants.DB_SCRIPT).split(";");
                conn = openConnection();
                Statement stmt = conn.createStatement();
                for (int i = 0; i < sqlStatements.length; i++)
                {
                    stmt.addBatch(sqlStatements[i]);
                }
                stmt.executeBatch();
                conn.commit();
                init = true;
            }
            catch (SQLException ex)
            {
                try
                {
                    conn.rollback();
                    ex.printStackTrace();
                }
                catch (SQLException ex1)
                {
                    ex.printStackTrace();
                    throw new RuntimeException(ex1);
                }
            }
            finally
            {
                if (conn != null)
                {
                    try
                    {
                        conn.close();
                    }
                    catch (SQLException ex)
                    {
                        ex.printStackTrace();
                        throw new RuntimeException(ex);
                    }
                }
            }
        }
    }

    private Connection openConnection()
    {
        try
        {
            Class.forName(Constants.DRIVER).newInstance();
//            String derbypath = "../../../javadb/lib/";
//            String derbypath = Constants.DERBY_PATH;
            String url = "jdbc:derby:" + Constants.DB_NAME;
            Connection conn = DriverManager.getConnection(url + ";create=true");
            conn.setAutoCommit(false);
            return conn;
        }
        catch (Exception ex)
        {
            ex.printStackTrace();
        }
        return null;
    }

    public List<String> getMapListing()
    {
        List<String> mapList = new ArrayList<String>();
        Connection conn = openConnection();
        if (conn != null)
        {
            String sql = "select name from map";
            try
            {
                PreparedStatement stmt = conn.prepareStatement(sql);
                ResultSet result = stmt.executeQuery();
                while (result.next())
                {
                    mapList.add(result.getString("name"));
                }
                conn.commit();
                return mapList;
            }
            catch (Exception ex)
            {
                ex.printStackTrace();
                try
                {
                    conn.rollback();
                }
                catch (SQLException ex1)
                {
                    ex1.printStackTrace();
                }
            }
            finally
            {
                try
                {
                    conn.close();
                }
                catch (Exception ex)
                {
                    ex.printStackTrace();
                }
            }
        }
        return null;

    }

    private boolean userProfileExists(String username)
    {
        Connection conn = openConnection();
        if (conn != null)
        {
            String sql = "select count(*) from userprofile where name=?";
            try
            {
                PreparedStatement stmt = conn.prepareStatement(sql);
                stmt.setString(1, username);
                ResultSet result = stmt.executeQuery();
                boolean exists = false;
                while (result.next())
                {
                    if (result.getInt(1) > 0)
                    {
                        exists = true;
                    }
                    exists = false;
                }
                conn.commit();
                return exists;
            }
            catch (Exception ex)
            {
                ex.printStackTrace();
                try
                {
                    conn.rollback();
                }
                catch (SQLException ex1)
                {
                    ex1.printStackTrace();
                }
            }
            finally
            {
                try
                {
                    conn.close();
                }
                catch (Exception ex)
                {
                    ex.printStackTrace();
                }
            }
        }
        return false;

    }

    public void addUserProfile(UserProfile userProfile)
    {
        if (!userProfileExists(userProfile.getName()))
        {
            Connection conn = openConnection();
            if (conn != null)
            {
                String sql = "insert into userprofile values(?,?,?,?)";
                try
                {
                    PreparedStatement stmt = conn.prepareStatement(sql);
                    stmt.setString(1, userProfile.getName());
                    stmt.setString(2, userProfile.getEmail());
                    stmt.setInt(3, userProfile.getGender());
                    stmt.setString(4, userProfile.getDob());
                    stmt.executeUpdate();
                    conn.commit();
                }
                catch (Exception ex)
                {
                    ex.printStackTrace();
                    try
                    {
                        conn.rollback();
                    }
                    catch (SQLException ex1)
                    {
                        ex1.printStackTrace();
                    }
                }
                finally
                {
                    try
                    {
                        conn.close();
                    }
                    catch (Exception ex)
                    {
                        ex.printStackTrace();
                    }
                }
            }
        }
    }

    public List<ViewScoreBean> getAllScore()
    {
        List<ViewScoreBean> scoreList = new ArrayList<ViewScoreBean>();
        String sql = "select * from score order by mapname asc,score desc";
        Connection conn = openConnection();
        if (conn != null)
        {
            try
            {
                PreparedStatement stmt = conn.prepareStatement(sql);
                ResultSet result = stmt.executeQuery();
                while (result.next())
                {
                    ViewScoreBean score = new ViewScoreBean();
                    score.setScore(result.getString("score"));
                    score.setMapname(result.getString("mapname"));
                    score.setUsername(result.getString("name"));
                    scoreList.add(score);
                }
                conn.commit();
                return scoreList;
            }
            catch (Exception ex)
            {
                ex.printStackTrace();
                try
                {
                    conn.rollback();
                }
                catch (SQLException ex1)
                {
                    ex1.printStackTrace();
                }
            }
            finally
            {
                try
                {
                    conn.close();
                }
                catch (Exception ex)
                {
                    ex.printStackTrace();
                }
            }
        }
        return null;
    }

    public void addScore(String name, int score, String mapname)
    {
        Connection conn = openConnection();
        if (conn != null)
        {
            String sql = "insert into score values(?,?,?)";
            try
            {
                PreparedStatement stmt = conn.prepareStatement(sql);
                stmt.setString(1, name);
                stmt.setInt(2, score);
                stmt.setString(3, mapname);
                stmt.executeUpdate();
                conn.commit();
            }
            catch (Exception ex)
            {
                ex.printStackTrace();
                try
                {
                    conn.rollback();
                }
                catch (SQLException ex1)
                {
                    ex1.printStackTrace();
                }
            }
            finally
            {
                try
                {
                    conn.close();
                }
                catch (Exception ex)
                {
                    ex.printStackTrace();
                }
            }
        }
    }
}
